********************************************************************************
** #3 election&population_data 

** #3-1 election data
** election data are on the province level as the superintendent of education are elected by province
** convert election data into dta file

import excel "election_data.xlsx", sheet("Sheet1") firstrow
save "election_data", replace 

clear

** #3-2 population data 

import excel "population_data.xlsx", sheet("???") firstrow

drop A
rename B pop2005
rename C pop2006
rename D pop2007
rename E pop2008
rename F pop2009
rename G pop2010
rename H pop2011
rename I pop2012
rename J pop2013
rename K pop2014
replace pop2014="." if pop2014 == "-"
destring pop2014, replace

gen district=.
gen dum=1
gen num=sum(dum)

forval i=1/230 {

local dist: word `i' of 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 ///
1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 ///
2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2131 ///
2201 2202 2203 2204 2205 2206 2207 2231 2301 2302 2303 2304 2305 2306 2307 2308 2331 2332 ///
2401 2402 2403 2404 2405 2501 2502 2503 2504 2505 2601 2602 2603 2604 2631 ///
3101 3102 3103 3104 3105 3106 3107 3108 3109 3110 3111 3112 3113 3114 3115 ///
3116 3117 3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 3132 3135 3137 3138 ///
3201 3202 3203 3204 3205 3206 3207 3231 3232 3233 3234 3235 3236 3237 3238 3239 3240 3241 ///
3301 3302 3303 3331 3332 3333 3334 3339 3335 3336 3337 3338 ///
3401 3402 3403 3404 3405 3406 3407 3431 3432 3433 3434 3435 3436 3437 3438 3439 ///
3501 3502 3503 3504 3505 3506 3531 3532 3533 3534 3535 3536 3537 3538 ///
3601 3602 3603 3604 3606 3631 3632 3633 3635 3636 3637 ///
3638 3639 3640 3641 3642 3643 3644 3645 3646 3647 3648 ///
3701 3702 3703 3704 3705 3706 3707 3708 3709 3710 3731 3732 ///
3733 3734 3735 3736 3737 3738 3739 3740 3741 3742 3743 ///
3811 3803 3805 3806 3807 3808 3809 3810 3831 3832 3833 3834 3835 3836 3837 3838 3839 3840 ///
3901 3902

replace district=`dist' if num == `i'

}

drop *um

reshape long pop, i(district) j(year)

gen province=int(district/100)
order province district year

save "pop_data", replace 

merge m:m province year using "election_data"
drop _merge A

gen logratio_liberal=log(election_libpct/election_conpct)

********************************************************************************
